﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServiciosGeograficos.Persistencia;
using System.Data;

namespace ServiciosGeograficos.Reconocimiento
{
    class InterpreteLineas
    {
        private PostgresDAO postgresDAO;


        // Dada una linea y el destino devuelve los posibles destinos de esa linea
        private DataSet posiblesDestinos(String destino, String linea)
        {
            String query = "select distinct linea.destino as destino,linea.linea as linea from lineas linea";
            query += " where levenshtein(linea.destino,'" + destino.ToUpper() + "') < 4 ";
            query += " and linea.linea = " + linea.ToUpper();
            query += " UNION ALL ";
            query += " select distinct linea.destino as destino,linea.linea as linea from lineas linea ";
            query += " where linea.linea = " + linea.ToUpper();
            return postgresDAO.ejecutarQuery(query, "posiblesDestinos");
        }

        // Dada una linea devuelve las posibles lineas
        private DataSet posiblesLineas(String linea)
        {
            String query = "select distinct linea.destino as destino,linea.linea as linea, levenshtein(linea.linea,'" + linea.ToUpper() + "') as levenshtein from lineas linea";
            query += " where levenshtein(linea.linea,'" + linea.ToUpper() + "') < 4 ";
            query += " order by levenshtein(linea.linea,'" + linea.ToUpper() + "') asc ";
            query += " limit 5";
            return postgresDAO.ejecutarQuery(query, "posiblesLineas");
        }

        // Dado el nombre de linea y un destino valida si los datos son correctos
        private bool encontreDestino(String destino, String linea)
        {
            String query = "select destino from lineas linea";
            query += " where linea.destino like '%" + destino + "%'";
            query += " and linea.linea = '" + linea + "'";
            DataSet dsPosiblesCalles = postgresDAO.ejecutarQuery(query, "posiblesLineas");
            return dsPosiblesCalles.Tables["posiblesLineas"].Rows.Count == 1;
        }

        // Dado el nombre de linea y un destino valida si los datos son correctos
        private bool encontreLinea(String linea)
        {
            String query = "select linea from lineas linea";
            query += " where linea.linea = '" + linea + "'";
            DataSet dsPosiblesCalles = postgresDAO.ejecutarQuery(query, "posiblesLineas");
            return dsPosiblesCalles.Tables["posiblesLineas"].Rows.Count >= 1;
        }

        public DataSet lineaDestino(String linea, String destino)
        {
            this.postgresDAO = new PostgresDAO();
            if (!encontreLinea(linea))
            {
                // No encontre la linea, devuelvo las posibles lineas
                return posiblesLineas(linea);
            }
            else if (!encontreDestino(destino, linea))
            {
                // No encontre destino, devuelvo los posibles destinos
                return posiblesDestinos(destino, linea);
            }
            else
            {
                String query = "select gid as gid, id as idBus from lineas linea ";
                query += " where linea.destino like '%" + destino + "%'";
                query += " and linea.linea = '" + linea + "'";
                return postgresDAO.ejecutarQuery(query, "lineas");
            }
        }

    }
}
